<?php

class VASModuleTimes extends Zend_Db_Table_Abstract
{
    protected $_name = 'module_times';
	
	public function enteringModule( $dumm1, $dumm2, $dumm3 )
	{
	}
	
	public function exitingModule( $dumm1, $dumm2, $dumm3 )
	{
	}
	
	public function recordModuleEntry( $module_name, $case_num, $locale_id=-1, $branch_number=-1, $license_number="" )
	{
		$results = array();
		
		$date = date('Y-m-d');
		$time = date('h:m:s');
		
		$timing_entry = array();
		
		$timing_entry['FDMS_case_number'] = $case_num;
		$timing_entry['locale_id'] = $locale_id;
		$timing_entry['branch_number'] = $branch_number;
		$timing_entry['FDMS_license_number'] = $license_number;
		$timing_entry['module_name'] = $module_name;
		$timing_entry['txntype'] = "ENTRY";
		$timing_entry['date'] = $date;
		$timing_entry['time'] = $time;
		$timing_entry['bypassed'] = false;
		$timing_entry['seconds'] = time();
		
		try
		{
			$r = $this->insert( $timing_entry );
			$results['status'] = "SUCCEEDED";
			$results['rows_affected'] = $r;
		}
		catch(Exception $e)
		{
			$results['status'] = 'FAILED';
			$results['reason'] = "".$e;
		}
		
		return $results;
	}
	
	public function recordModuleExit( $module_name, $case_num, $locale_id, $branch_number, $license_number )
	{
		$results = array();
		
		$date = date('Y-m-d');
		$time = date('h:m:s');
		
		$timing_entry = array();
		
		$timing_entry['FDMS_case_number'] = $case_num;
		$timing_entry['locale_id'] = $locale_id;
		$timing_entry['branch_number'] = $branch_number;
		$timing_entry['FDMS_license_number'] = $license_number;
		$timing_entry['module_name'] = $module_name;
		$timing_entry['txntype'] = "EXIT";
		$timing_entry['date'] = $date;
		$timing_entry['time'] = $time;
		$timing_entry['bypassed'] = false;
		$timing_entry['seconds'] = time();
		
		try
		{
			$r = $this->insert( $timing_entry );
			$results['status'] = "SUCCEEDED";
			$results['rows_affected'] = $r;
		}
		catch(Exception $e)
		{
			$results['status'] = 'FAILED';
			$results['reason'] = "".$e;
		}
		
		return $results;
	}
	
	public function recordModuleTime( $module_name, $case_num, $locale_id, $branch_number, $license_number, $seconds )
	{
		$results = array();
		/*
		$date = date('Y-m-d');
		$time = date('h:m:s');
		
		$timing_entry = array();
		
		$timing_entry['FDMS_case_number'] = $case_num;
		$timing_entry['locale_id'] = $locale_id;
		$timing_entry['branch_number'] = $branch_number;
		$timing_entry['FDMS_license_number'] = $license_number;
		$timing_entry['module_name'] = $module_name;
		$timing_entry['txntype'] = "TIME";
		$timing_entry['date'] = $date;
		$timing_entry['time'] = $time;
		$timing_entry['bypassed'] = false;
		$timing_entry['seconds'] = $seconds;
		
		try
		{
			$r = $this->insert( $timing_entry );
			$results['status'] = "SUCCEEDED";
			$results['rows_affected'] = $r;
		}
		catch(Exception $e)
		{
			$results['status'] = 'FAILED';
			$results['reason'] = "".$e;
		}
		*/
		return $results;
	}
	
	public function recordTimeInModule( $module_name, 
		$case_num, $locale_id, $branch_number, $region, $arranger_id, $seconds )
	{
		$results = array();
		
		$date = date('Y-m-d');
		$time = date('h:m:s');
		
		$timing_entry = array();
		
		$timing_entry['FDMS_case_number'] = $case_num;
		$timing_entry['locale_id'] = $locale_id;
		$timing_entry['branch_number'] = $branch_number;
		$timing_entry['region'] = $region;
		$timing_entry['arranger_id'] = $arranger_id;
		$timing_entry['module_name'] = $module_name;
		$timing_entry['txntype'] = "TIME";
		$timing_entry['date'] = $date;
		$timing_entry['time'] = $time;
		$timing_entry['bypassed'] = false;
		$timing_entry['seconds'] = $seconds;
		
		try
		{
			$r = $this->insert( $timing_entry );
			$results['status'] = "SUCCEEDED";
			$results['rows_affected'] = $r;
		}
		catch(Exception $e)
		{
			$results['status'] = 'FAILED';
			$results['reason'] = "".$e;
		}

		return $results;
	}
	
	public function moduleTimesForCase( $case_num )
	{
 		$rows = $this->fetchAll( "FDMS_case_number='$case_num'" ); // order by module_name and txntype

 		$module_times = array();
		
 		foreach($rows as $row)
 		{
 		 	$module_name = $row["module_name"];
			$module_times[$module_name] += $row['seconds'];
		}
		
		return $module_times;
	}
	
	// general module stats:
	// avg time 
	
	public function statsForArranger( $arranger_id )
	{
		// select count(*) as n, sum(seconds)/60.0 as minutes_per_case from module_times where arranger_id = $arranger_id group by FDMS_case_num
		$select = $this->select();
		$select->from( $this, array( 'FDMS_case_number as case_number', 'count(seconds) as num_modules_visited', 'sum(seconds)/60.0 as total_minutes_spent' ))->where("arranger_id = $arranger_id")->group('FDMS_case_number');
		
		$returned_rows = array();
		$rows = $this->fetchAll($select);
		
		$min_time = 999999.99;
		$max_time = -1;
		$sum_time = 0.0;
		$num_cases = 0;
		foreach($rows as $row)
		{
			$returned_rows[] = $row->toArray();
			
			if($row->total_minutes_spent > $max_time) $max_time = $row->total_minutes_spent;
			if($row->total_minutes_spent < $min_time) $min_time = $row->total_minutes_spent;
			$sum_time += $row->total_minutes_spent;
			$num_cases++;
		}
		
		if($num_cases>0)
		{
			$avg_time = $sum_time / $num_cases;
		}
		else
		{
			$avg_time = 0;
		}
		
		return array('min_casetime'=>$min_time, 'max_casetime'=>$max_time, 'num_cases'=>$num_cases, 'avg_casetime'=>$avg_time );
	}
	
	public function statsForBranch( $branch_number )
	{
		//var_dump($branch_number);
		// select count(*) as n, sum(seconds)/60.0 as minutes_per_case from module_times where branch_number = $branch_number group by FDMS_case_num
		$select = $this->select();
		$select->from( $this, array( 'FDMS_case_number as case_number', 'count(seconds) as num_modules_visited', 'sum(seconds)/60.0 as total_minutes_spent' ))->where("branch_number = $branch_number")->group('FDMS_case_number');
		
		$returned_rows = array();
		$rows = $this->fetchAll($select);
		$min_time = 999999.99;
		$max_time = -1;
		$sum_time = 0.0;
		$num_cases = 0;
		foreach($rows as $row)
		{
			$returned_rows[] = $row->toArray();
			
			if($row->total_minutes_spent > $max_time) $max_time = $row->total_minutes_spent;
			if($row->total_minutes_spent < $min_time) $min_time = $row->total_minutes_spent;
			$sum_time += $row->total_minutes_spent;
			$num_cases++;
		}
		
		//var_dump($returned_rows);
		if($num_cases>0)
		{
			$avg_time = $sum_time / $num_cases;
		}
		else
		{
			$avg_time = 0;
		}
		
		return array('min_casetime'=>$min_time, 'max_casetime'=>$max_time, 'num_cases'=>$num_cases, 'avg_casetime'=>$avg_time );
	}
	
	public function statsForRegion( $region )
	{
                if(!isset($region) || empty($region)) $region = -1;
		// select count(*) as n, sum(seconds)/60.0 as minutes_per_case from module_times where branch_number = $branch_number group by FDMS_case_num
		$select = $this->select();
		$select->from( $this, array( 'FDMS_case_number as case_number', 'count(seconds) as num_modules_visited', 
		'sum(seconds)/60.0 as total_minutes_spent' ))->where("region = $region")->group('FDMS_case_number');
		
		$returned_rows = array();
		$rows = $this->fetchAll($select);
		$min_time = 999999.99;
		$max_time = -1;
		$sum_time = 0.0;
		$num_cases = 0;
		foreach($rows as $row)
		{
			$returned_rows[] = $row->toArray();
			
			if($row->total_minutes_spent > $max_time) $max_time = $row->total_minutes_spent;
			if($row->total_minutes_spent < $min_time) $min_time = $row->total_minutes_spent;
			$sum_time += $row->total_minutes_spent;
			$num_cases++;
		}
//Zend_Debug::dump($region);
		//Zend_Debug::dump($returned_rows);
		if($num_cases>0)
		{
			$avg_time = $sum_time / $num_cases;
		}
		else
		{
			$avg_time = 0;
		}
		
		return array('min_casetime'=>$min_time, 'max_casetime'=>$max_time, 'num_cases'=>$num_cases, 'avg_casetime'=>$avg_time );
	}
	
	public function statsForCompany()
	{
		$select = $this->select();
		$select->from( $this, array( 'FDMS_case_number as case_number', 'count(seconds) as num_modules_visited', 
		'sum(seconds)/60.0 as total_minutes_spent' ))->where("1")->group('FDMS_case_number');
		
		$returned_rows = array();
		$rows = $this->fetchAll($select);
		$min_time = 999999.99;
		$max_time = -1;
		$sum_time = 0.0;
		$num_cases = 0;
		foreach($rows as $row)
		{
			$returned_rows[] = $row->toArray();
			
			if($row->total_minutes_spent > $max_time) $max_time = $row->total_minutes_spent;
			if($row->total_minutes_spent < $min_time) $min_time = $row->total_minutes_spent;
			$sum_time += $row->total_minutes_spent;
			$num_cases++;
		}
		
		if($num_cases>0)
		{
			$avg_time = $sum_time / $num_cases;
		}
		else
		{
			$avg_time = 0;
		}
		
		return array('min_casetime'=>$min_time, 'max_casetime'=>$max_time, 'num_cases'=>$num_cases, 'avg_casetime'=>$avg_time );
	}
	
	// note -- for each of these next three we need to include a case count so we can do an average
	public function moduleStatsForArranger( $arranger_id )
	{
 		$rows = $this->fetchAll( "arranger_id=$arranger_id" ); // order by FDMS_case_number

 		$module_stats = array();

		$case_numbers = array();
		$module_names = array();
		$module_times = array();
		
 		foreach($rows as $row)
 		{
 			$case_number = $row['FDMS_case_number'];
 		 	$module_name = $row["module_name"];
		
			$case_numbers[$case_number] = 1;
			$module_names[$module_name] = 1;
			
			$total_time += $row['seconds'];
			$module_times[$module_name]['N'] += 1;
			$module_times[$module_name]['Total'] += $row['seconds'];
		}
		
		$module_stats['num_cases'] = count(array_keys($case_numbers));
		if($module_stats['num_cases']>0)
			$module_stats['time_per_case'] = $total_time / count(array_keys($case_numbers));
		else
			$module_stats['time_per_case'] = 0;
			
		foreach($module_times as $module_name=>$tuple)
		{
			if($tuple['N']>0)
			{
				$module_times[$module_name]['Average'] = $tuple['Total'] / $tuple['N'];
			}
			else
			{
				$module_times[$module_name]['Average'] = 'NaN';
			}
		}
		
		$module_stats['num_cases'] = count(array_keys($case_numbers));
		if($module_stats['num_cases']>0)
			$module_stats['time_per_case'] = $total_time / count(array_keys($case_numbers));
		else
			$module_stats['time_per_case'] = 0;
		$module_stats['module_times'] = $module_times;
		return $module_stats;
	}

	// stats are avg time per case
	//           avg time per module	
	public function moduleStatsForBranch( $branch_num )
	{
 		$rows = $this->fetchAll( "branch_number=$branch_num" ); // order by FDMS_case_number

 		$module_stats = array();

		$case_numbers = array();
		$module_names = array();
		$module_times = array();
		
 		foreach($rows as $row)
 		{
 			$case_number = $row['FDMS_case_number'];
 		 	$module_name = $row["module_name"];
		
			$case_numbers[$case_number] = 1;
			$module_names[$module_name] = 1;
			
			$total_time += $row['seconds'];
			$module_times[$module_name]['N'] += 1;
			$module_times[$module_name]['Total'] += $row['seconds'];
		}
		
		$module_stats['num_cases'] = count(array_keys($case_numbers));
		if($module_stats['num_cases']>0)
			$module_stats['time_per_case'] = $total_time / count(array_keys($case_numbers));
		else
			$module_stats['time_per_case'] = 0;
		foreach($module_times as $module_name=>$tuple)
		{
			if($tuple['N']>0)
			{
				$module_times[$module_name]['Average'] = $tuple['Total'] / $tuple['N'];
			}
			else
			{
				$module_times[$module_name]['Average'] = 'NaN';
			}
		}
		
		$num_cases = count(array_keys($case_numbers));
		if($num_cases>0)
		{
			$module_stats['num_cases'] = count(array_keys($case_numbers));
			$module_stats['time_per_case'] = $total_time / count(array_keys($case_numbers));
			$module_stats['module_times'] = $module_times;
		}
		else
		{
			$module_stats['num_cases'] = 0;
			$module_stats['time_per_case'] = -1;
			$module_stats['module_times'] = array();
		}
		
		return $module_stats;
	}
	
	public function moduleStatsForLocale( $locale_id )
	{
 		$rows = $this->fetchAll( "locale_id=$locale_id" ); // order by FDMS_case_number

 		$module_stats = array();

		$case_numbers = array();
		$module_names = array();
		
 		foreach($rows as $row)
 		{
 			$case_number = $row['FDMS_case_number'];
 		 	$module_name = $row["module_name"];
		
			$case_numbers[$case_number] = 1;
			$module_names[$module_name] = 1;
			
			$total_time += $row['seconds'];				
		}
		
		$num_cases = count(array_keys($case_numbers));
		if($num_cases>0)
		{
			$module_stats['num_cases'] = count(array_keys($case_numbers));
			$module_stats['time_per_case'] = $total_time / count(array_keys($case_numbers));
			$module_stats['num_modules'] = count(array_keys($module_names));
			$module_stats['time_per_module'] = $total_time / count(array_keys($module_names));
		}
		else
		{
			$module_stats['num_cases'] = 0;
			$module_stats['time_per_case'] = -1;
			$module_stats['num_modules'] = 0;
			$module_stats['time_per_module'] = -1;
		}
		
		return $module_stats;
	}
	
	// statistics - max, min, and average time per case	
	public function companyWideCaseStats()
	{
 		$rows = $this->fetchAll();
	}
	
	public function regionWideCaseStats( $region )
	{
		// get branch numbers for this region and do a fetchAll() with an IN clause
		$homes = new FuneralHomes();
		$results = $homes->getFuneralHomesInRegion($region);
		$branches = $results['rows'];
		$branches_str = implode(",",$branches);
 		$rows = $this->fetchAll( "branch_number IN ($branches_str)" ); // order by FDMS_case_number

 		$module_stats = array();

		$case_numbers = array();
		$module_names = array();
		
 		foreach($rows as $row)
 		{
 			$case_number = $row['FDMS_case_number'];
 		 	$module_name = $row["module_name"];
		
			$case_numbers[$case_number] = 1;
			$module_names[$module_name] = 1;
			
			$total_time += $row['seconds'];				
		}
		
		$num_cases = count(array_keys($case_numbers));
		if($num_cases>0)
		{
			$module_stats['num_cases'] = count(array_keys($case_numbers));
			$module_stats['time_per_case'] = $total_time / count(array_keys($case_numbers));
			$module_stats['num_modules'] = count(array_keys($module_names));
			$module_stats['time_per_module'] = $total_time / count(array_keys($module_names));
		}
		else
		{
			$module_stats['num_cases'] = 0;
			$module_stats['time_per_case'] = -1;
			$module_stats['num_modules'] = 0;
			$module_stats['time_per_module'] = -1;
		}
		
		return $module_stats;
	}
	
	public function branchCaseStats( $branch_number )
	{
	}
	
	public function arrangerCaseStats( $arranger_license )
	{
	}
}

?>